package com.itheima.jdbcdemo;


import com.itheima.pojo.Account;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * template实现增删改
 */
public class JdbcDemo3 {
    /**
     * 获取模板对象的方法
     *
     * @return 模板对象
     */
    private static JdbcTemplate method() {
        //创建数据源对象
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        //注册驱动
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        //sql用户名
        dataSource.setUsername("root");
        //sql密码
        dataSource.setPassword("");
        //sql连接地址
        dataSource.setUrl("jdbc:mysql://192.168.203.128:3306/case2test");
        //创建模板对象
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        //将数据传输给spring封装好的模板
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }


    @Test
    public void add() {
        //新增用户
        String sql = "insert into account values(null,?,?)";
        int count = method().update(sql, "刘诗诗", 10000f);
        System.out.println(count);
    }

    @Test
    public void updat() {
        //修改用户
        String sql = "update account set name=?,money=? where id=?";
        //返回影响的行数
        int count = method().update(sql, "ccc", 900f, 3);
        //打印结果
        System.out.println("影响的行数: " + count);

    }

    @Test
    public void delete() {
        //删除用户
        String sql = "delete from account where id =?";
        int count = method().update(sql, 3);
        System.out.println(count);
    }

    @Test
    public void select() {
        //查询结果单行单列
        String sql = "select name from account where id=?";

        String name = method().queryForObject(sql, String.class, 4);
        System.out.println(name);
    }

    @Test
    public void select1() {
        //查询结果为单行多列
        String sql = "select * from account where id =?";
        Map<String, Object> queryForMap = method().queryForMap(sql, 4);

        System.out.println(queryForMap);
    }

    @Test
    public void select3() {
        //查询结果为多行单列
        String sql = "select name  from account ";

        List<Map<String, Object>> list = method().queryForList(sql);

        System.out.println(list);

    }


    @Test
    public void select4() {
        //查询结果为多行多列
        String sql = "select * from account";

        List<Account> accountList = method().query(sql, new BeanPropertyRowMapper<>(Account.class));

        System.out.println(accountList);
    }
}
